#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2023/10/9 16:30
# @Author : 闫峰
# @File    : ceshi.py
from common.setting import ensure_path_sep
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.sql_server_utils import MSSQL


class ClassTest:

    def test001(self):
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        # sql1 = """
        # SELECT
        #     HandleAdminID,
        #     HandleUserName,
        #     PlatformId,
        #     CountrySite,
        #     AddAdminID,
        #     AddUserName,
        #     ReceiveTime ,
        #     HandleTime,
        #     Asin,
        #     AddTime
        # FROM
        #     zhcxkj_autochoose.infringement_email
        # WHERE
        #     HandleAdminID=8700
        #     and PlatformId=3
        #     and CountrySite='DE'
        #     and AddAdminID=1258
        # ORDER BY ReceiveTime ,HandleTime
        # """
        # res = db.fetch_all_tuple(sql1, ())
        # count = 0
        # second_total = 0
        # for i in res:
        #
        #     if i[6] != None and i[7] != None:
        #         if i[7] > i[6]:
        #             count +=1
        #             time_second = (i[7]-i[6]).total_seconds()
        #             second_total +=time_second
        # day = round(second_total/count / (24 * 60 * 60),2)
        # print(day)

        sql2 = """
        SELECT
            SKU,PublishTime,OnlineTime,ReceiveTime
            FROM zhcxkj_autochoose.infringement_email 
        WHERE
            SKU='11956450'
        """
        res2 = db.fetch_all_tuple(sql2, ())
        print()
        PublishTime_count = 0
        PublishTime_total = 0
        for data in res2:
            if data[1]:
                PublishTime_count += 1
                second_one = abs((data[1] - data[3]).total_seconds())
                PublishTime_total += second_one
        day = round(PublishTime_total / PublishTime_count / (24 * 60 * 60), 2)
        print()

    def test_002(self):
        """
        申诉情况 汇总
        :return:
        """
        PlatformId_list = [0, 1, 2, 3, 4, 5, 7, 8, 10, 99]
        for PlatformId in PlatformId_list:
            db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
            sql2 = """
            SELECT
                PlatformId,
                ReceiveTime,
                HandleTime,
                AppealTime,
                AppealResult
            FROM
                zhcxkj_autochoose.infringement_email 
            WHERE PlatformId=%s
            """
            res2 = db.fetch_all_tuple(sql2, (PlatformId,))
            # `AppealResult` int unsigned NOT NULL DEFAULT '0' COMMENT '（销售）申述结果 0-未申诉，1-申诉成功，2-申诉失败，3-放弃申诉，4-无法申诉',
            AppealResult_succuss = 0
            AppealResult_failure = 0
            AppealResult_abandon = 0
            AppealResult_no = 0
            AppealResult_other = 0
            limitation_of_appeal_total = 0
            limitation_of_appeal_count = 0
            for data in res2:
                if data[4] == 0:
                    continue
                elif data[4] == 1:
                    AppealResult_succuss += 1
                elif data[4] == 2:
                    AppealResult_failure += 1

                elif data[4] == 3:
                    AppealResult_abandon += 1
                elif data[4] == 4:
                    AppealResult_no += 1
                else:
                    AppealResult_other += 1
                if data[2] != None and data[3] != None:
                    limitation_of_appeal_second = abs((data[3] - data[2]).total_seconds())
                    limitation_of_appeal_total += limitation_of_appeal_second
                    limitation_of_appeal_count += 1
            if limitation_of_appeal_count:
                day = round(limitation_of_appeal_total / limitation_of_appeal_count / (24 * 60 * 60), 2)
            else:
                day = 0
            print()

    def test_003(self):
        """
        邮件处理情况-汇总 - 侵权邮件
        :return:
        """
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

        sql1 = """
        SELECT HandleAdminID,PlatformId,CountrySite,AddAdminID FROM zhcxkj_autochoose.infringement_email GROUP BY HandleAdminID,PlatformId,CountrySite,AddAdminID
        """
        res1 = db.fetch_all_tuple(sql1, ())
        for data in res1:
            data = (8700, 1, "US", 7438)
            sql = """
            SELECT
            HandleTime,ReceiveTime
            FROM
                zhcxkj_autochoose.infringement_email 
            WHERE 
                HandleAdminID=%s
                and PlatformId=%s
                and CountrySite=%s
                and AddAdminID=%s
            """
            res = db.fetch_all_tuple(sql, (data[0], data[1], data[2], data[3]))
            res_count = 0
            res_total = 0
            for i in res:
                if i[0] is not None and i[1] is not None:
                    if i[0] > i[1]:
                        res_count += 1
                        second_one = abs((i[0] - i[1]).total_seconds())
                        res_total += second_one
            if res_count != 0:
                day = round(res_total / res_count / (24 * 60 * 60), 2)
            else:
                day = None
            print()

    def test_004(self):
        """
        新政策移除情况-汇总
        :return:
        """
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        sql1 = """
        SELECT PlatformId,CountrySite FROM zhcxkj_autochoose.prohibited_email 
        GROUP BY PlatformId,CountrySite
        """
        # args_list = db.fetch_all_tuple(sql1, ())
        args_list = [(1, '')]
        for data in args_list:
            sql2 = """
            SELECT
            PlatformId,
            CountrySite,PublishTime,ReceiveTime,IsNewPolicyRemoved
        FROM
            zhcxkj_autochoose.prohibited_email 
        WHERE
            PlatformId=%s
            and CountrySite=%s
            """
            res2 = db.fetch_all_tuple(sql2, (data[0], data[1]))
            res_count = 0
            res_total = 0
            IsNewPolicyRemoved_1 = 0

            for data1 in res2:

                if data1[4]:
                    if int.from_bytes(data1[4], byteorder='big', signed=True) == 1:
                        IsNewPolicyRemoved_1 += 1
                if data1[2] is not None:
                    if data1[3] > data1[2]:
                        res_count += 1
                        second_one = abs((data1[2] - data1[3]).total_seconds())
                        res_total += second_one
            if res_count != 0:
                day = round(res_total / res_count / (24 * 60 * 60), 2)
            else:
                day = None
            print()

    def test_005(self):
        """
        误判及申诉
        :return:
        """
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        # sql1 = """
        # SELECT PlatformId,CountrySite,PublishType FROM zhcxkj_autochoose.prohibited_email
        # WHERE ProhibitedType=4
        # GROUP BY PlatformId,CountrySite,PublishType
        # """
        # args_list = db.fetch_all_tuple(sql1, ())
        # print(args_list)
        res_count = 0
        res_total = 0
        args_list = [(1, "AU", 9)]
        for data in args_list:
            sql2 = """
            SELECT PlatformId,CountrySite,PublishType,AppealTime,HandleTime FROM zhcxkj_autochoose.prohibited_email 
                WHERE ProhibitedType=4
                and PlatformId=%s
                and CountrySite=%s
                and PublishType=%s
            """
            res2 = db.fetch_all_tuple(sql2, (data[0], data[1], data[2]))
            for i in res2:
                if i[3] is not None and i[4] is not None:
                    res_count += 1
                    second_one = abs((i[3] - i[4]).total_seconds())
                    res_total += second_one
            if res_count != 0:
                day = round(res_total / res_count / (24 * 60 * 60), 2)
            else:
                day = None
            print()

    def test_006(self):
        """
        邮件处理情况-汇总 -- 违禁邮件
        :return:
        """
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

        sql1 = """
        SELECT HandleAdminID FROM zhcxkj_autochoose.prohibited_email 
    GROUP BY HandleAdminID
        """
        res1 = db.fetch_all_tuple(sql1, ())
        res1 = [(8642,)]
        for data in res1:
            sql = """
            SELECT HandleTime,ReceiveTime FROM zhcxkj_autochoose.prohibited_email
            WHERE HandleAdminID=%s

            """
            res = db.fetch_all_tuple(sql, (data[0],))
            res_count = 0
            res_total = 0
            for i in res:
                if i[0] is not None and i[1] is not None:
                    if i[0] > i[1]:
                        res_count += 1
                        second_one = abs((i[0] - i[1]).total_seconds())
                        res_total += second_one
            if res_count != 0:
                day = round(res_total / res_count / (24 * 60 * 60), 2)
            else:
                day = None
            print()

    def test_007(self):
        """
        产品SKU违禁-汇总 -- 违禁邮件
        :return:
        """
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

        sql1 = """
            SELECT SKU  FROM zhcxkj_autochoose.prohibited_email GROUP BY SKU
        """
        res1 = db.fetch_all_tuple(sql1, ())
        res1 = [("16510916",)]
        for data in res1:
            sku = data[0]
            sql = """
                SELECT
                    HandleTime,
                    ReceiveTime 
                FROM
                    zhcxkj_autochoose.prohibited_email 
                WHERE
                    sku = %s
            """
            res = db.fetch_all_tuple(sql, (sku,))
            res_count = 0
            res_total = 0
            for i in res:
                if i[0] is not None and i[1] is not None:
                    if i[0] > i[1]:
                        res_count += 1
                        second_one = abs((i[0] - i[1]).total_seconds())
                        res_total += second_one
            if res_count != 0:
                day = round(res_total / res_count / (24 * 60 * 60), 2)
            else:
                day = None
            print()

    def test_008(self):
        """
        禁售邮件管理-明细 -- 违禁邮件
        :return:
        """
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

        sql1 = """
            SELECT HandleTime,AppealTime,ReceiveTime
        FROM zhcxkj_autochoose.prohibited_email  
        WHERE id=98326
        """
        res1 = db.fetch_all_tuple(sql1, ())

        for data in res1:
            second_one = abs((data[0] - data[2]).total_seconds())
            second_two = abs((data[1] - data[0]).total_seconds())
            day1 = round(second_one / (24 * 60 * 60), 2)
            day2 = round(second_two / (24 * 60 * 60), 2)
            print()

    def test_009(self):
        """
        侵权邮件管理-明细 -- 违禁邮件
        :return:
        """
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

        sql1 = """
            SELECT HandleTime,AppealTime,ReceiveTime
        FROM zhcxkj_autochoose.infringement_email  
        WHERE id=11596
        """
        res1 = db.fetch_all_tuple(sql1, ())

        for data in res1:
            second_one = abs((data[0] - data[2]).total_seconds())
            second_two = abs((data[1] - data[0]).total_seconds())
            day1 = round(second_one / (24 * 60 * 60), 2)
            day2 = round(second_two / (24 * 60 * 60), 2)
            print()

    def test_010(self):
        """

        :return:
        """
        db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        sql_db = MSSQL(ensure_path_sep("\\config\\config.yaml"), "erp_server")
        sql = """
        SELECT id FROM [dbo].[E_KeyWords_Sale] WHERE KeyName = %s
        """
        sql2 = """
        SELECT PlatformId,CountrySite,PublishType,ProhibitedWord 
        FROM zhcxkj_autochoose.prohibited_email
        where ProhibitedWord !=""
        GROUP BY PlatformId,CountrySite,PublishType,BINARY ProhibitedWord
        """
        ProhibitedWord_list = db.fetch_all_tuple(sql2, ())
        ProhibitedWord_list = list(map(lambda x : x[0],ProhibitedWord_list))
        for ProhibitedWord in ProhibitedWord_list:
            res = sql_db.ExecQuery(sql, (ProhibitedWord,))
            print()




if __name__ == '__main__':
    # ClassTest().test001()
    # ClassTest().test_002()
    # ClassTest().test_003()
    ClassTest().test_007()
